package com.common.util;

import cn.hutool.core.util.StrUtil;
import org.apache.commons.lang.time.DateUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.json.JSONObject;

import java.io.*;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 公共导出excel工具类
 * 
 * @author yyq
 *
 */
public class ExcelUtil {

	/**
	 * 导出excel数据
	 * @param title
	 * @param path
	 * @param titleArray
	 * @param cloumArray
	 * @param list
	 * @throws IOException
	 */
	public static void export(String title, String path, String[] titleArray, String [] cloumArray, List<Map<String, Object>> list) throws IOException {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(title);
		HSSFCellStyle styleBg = getFont(wb, 0);
		HSSFCellStyle styleBg1 = getFont(wb, 1);
		HSSFRow row = sheet.createRow(0);
		row.setHeightInPoints(20);

		for (int i = 0; i < titleArray.length; i++) {
			HSSFCell celltitle = row.createCell(i);// 列
			celltitle.setCellValue(titleArray[i]);// 设置单元格的值
			celltitle.setCellStyle(styleBg1);
			sheet.setColumnWidth(i, (titleArray[i].length() + 20) * 256);
		}
		int rowNum = 1;
		for (Map<String, Object> map : list) {
			row = sheet.createRow(rowNum);// 创建行
			row.setHeightInPoints(30);
			for (int j = 0; j < cloumArray.length; j++) {
				HSSFCell celltitle = row.createCell(j); // 创建列
				Object obj = map.get(cloumArray[j]);
				celltitle.setCellValue(obj == null ? "" : obj + "");
				celltitle.setCellStyle(styleBg);
			}
			rowNum ++;
		}
		File file = new File(path);
		if (!file.getParentFile().exists()) {// 先创建目录和文件
			file.getParentFile().mkdirs();
			file.createNewFile();
		}
		FileOutputStream fout = new FileOutputStream(path);
		wb.write(fout);
		fout.close();
	}

	
	/**
	 * 导入excel数据
	 * @param file
	 * @param titleLength
	 * @return
	 * @throws IOException
	 */
	public static List<List<Object>> importExcel(File file, int titleLength) throws IOException{ 
		String fileName = file.getName();    
		String extension = fileName.lastIndexOf(".")==-1?"":fileName.substring(fileName.lastIndexOf(".")+1);    
		if("xls".equals(extension)){ 
			return read2003Excel(file,titleLength);    
		}else if("xlsx".equals(extension)){ 
			return read2007Excel(file,titleLength);    
		}else{ 
			throw new IOException("不支持的文件类型");    
		} 
	} 
	
	/**  
    * 读取 office 2003 excel  
    * @throws IOException   
    * @throws FileNotFoundException 
    */ 
	private static List<List<Object>> read2003Excel(File file, int titleLenght) throws IOException{ 
		List<List<Object>> list = new LinkedList<List<Object>>();    
		HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));    
		HSSFSheet sheet = hwb.getSheetAt(0);    
		Object value = null;    
		HSSFRow row = null;    
		HSSFCell cell = null;
		int tempCount = 0;
		int length = sheet.getRow(titleLenght).getLastCellNum(); // 获取标题的长度
		for(int i = sheet.getFirstRowNum();i<= sheet.getPhysicalNumberOfRows(); i++){    
			row = sheet.getRow(i);    
			if (row == null) { 
				continue;    
			}
			// 导入限制一万笔数据
			tempCount++;
			if(tempCount > 10000) {
				break;
			}
			List<Object> linked = new LinkedList<Object>();    
			for (int j = 0; j < length; j++) {    
				cell = row.getCell(j);   
				if (cell == null) { 
					linked.add("");
					continue;    
				}
				int exists = (cell.toString()).indexOf("月");// 日期无法直接转字符串获取相应数据，所有先判断是否存在日期格式
				cell.setCellType(1);// 强制设置值为字符串类型
				if(exists != -1) {
					String date = importByExcelForDate(cell.getStringCellValue());
					if(date != null) {
						linked.add(date);
						continue;
					}
				}
				DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符    
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串    
				DecimalFormat nf = new DecimalFormat("0");// 格式化数字    
				switch (cell.getCellType()) { 
				case XSSFCell.CELL_TYPE_STRING: 
					// System.out.println(i+"行"+j+" 列 is String type");    
					value = cell.getStringCellValue();    
					break;    
				case XSSFCell.CELL_TYPE_NUMERIC: 
					// System.out.println(i+"行"+j+" 列 is Number type ; DateFormt:"+cell.getCellStyle().getDataFormatString());    
					if("@".equals(cell.getCellStyle().getDataFormatString())){ 
						value = df.format(cell.getNumericCellValue());    
					} else if("General".equals(cell.getCellStyle().getDataFormatString())){ 
						value = nf.format(cell.getNumericCellValue());
						//value = cell.getNumericCellValue()+""; 
					}else{ 
						value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));    
					} 
					break;   
				case XSSFCell.CELL_TYPE_BOOLEAN: 
					// System.out.println(i+"行"+j+" 列 is Boolean type");    
					value = cell.getBooleanCellValue();    
					break;    
				case XSSFCell.CELL_TYPE_BLANK: 
					// System.out.println(i+"行"+j+" 列 is Blank type");    
					value = "";    
					break;    
				default: 
					// System.out.println(i+"行"+j+" 列 is default type");    
					value = cell.toString();    
				} 
				// 注释这里之后空值也可以添加
				/*if (value == null || "".equals(value)) { 
					continue;    
				} */
				linked.add(value);      
			} 
			list.add(linked);    
		} 
		return list;    
	}
	
	/**  
    * 读取Office 2007 excel  
    */ 
	private static List<List<Object>> read2007Excel(File file,int titleLenght) throws IOException { 
		List<List<Object>> list = new LinkedList<List<Object>>();    
		// 构造 XSSFWorkbook 对象，strPath 传入文件路径 
		XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));    
		// 读取第一章表格内容 
		XSSFSheet sheet = xwb.getSheetAt(0);    
		Object value = null;    
		XSSFRow row = null;    
		XSSFCell cell = null; 
		int tempCount = 0;
		int length = sheet.getRow(titleLenght).getLastCellNum();// 获取标题的长度
		for (int i = sheet.getFirstRowNum(); i <= sheet.getPhysicalNumberOfRows(); i++) {
			row = sheet.getRow(i);    
			if (row == null) {
				continue;    
			}
			// 导入限制一万笔数据
			tempCount++;
			if(tempCount > 10000) {
				break;
			}
			List<Object> linked = new LinkedList<Object>();    
			for (int j = 0; j < length; j++) {    
				cell = row.getCell(j);    
				if (cell == null) { 
					linked.add("");
					continue;    
				}
				int exists = (cell.toString()).indexOf("月");// 日期无法直接转字符串获取相应数据，所有先判断是否存在日期格式
				cell.setCellType(1);// 强制设置值为字符串类型
				if(exists != -1) {
					String date = importByExcelForDate(cell.getStringCellValue());
					if(date != null) {
						linked.add(date);
						continue;
					}
				}
				DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符    
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串    
				DecimalFormat nf = new DecimalFormat("0");// 格式化数字   
				switch (cell.getCellType()) { 
				case XSSFCell.CELL_TYPE_STRING: 
					// System.out.println(i+"行"+j+" 列 is String type");    
					value = cell.getStringCellValue();    
					break;
				case XSSFCell.CELL_TYPE_NUMERIC: 
					// System.out.println(i+"行"+j+" 列 is Number type ; DateFormt:"+cell.getCellStyle().getDataFormatString());    
					if("@".equals(cell.getCellStyle().getDataFormatString())){ 
						value = df.format(cell.getNumericCellValue());    
					} else if("General".equals(cell.getCellStyle().getDataFormatString())){ 
						value = nf.format(cell.getNumericCellValue());
						//value = cell.getNumericCellValue()+""; 
					}else{ 
						value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));    
					} 
					break;    
				case XSSFCell.CELL_TYPE_BOOLEAN: 
					// System.out.println(i+"行"+j+" 列 is Boolean type");    
					value = cell.getBooleanCellValue();    
					break;    
				case XSSFCell.CELL_TYPE_BLANK: 
					// System.out.println(i+"行"+j+" 列 is Blank type");    
					value = "";    
					break;    
				default: 
					// System.out.println(i+"行"+j+" 列 is default type");    
					value = cell.toString();    
				} 
				// 注释这里之后空值也可以添加
				/*if (value == null || "".equals(value)) { 
					continue;    
				} */
				linked.add(value);    
			} 
			list.add(linked);    
		} 
		return list;    
	}

	/**
	 * 获取到的日期天数转日期
	 * @param value
	 * @return
	 */
	public static String importByExcelForDate(String value) {
		String currentCellValue = "";
		if(value != null && !value.equals("")){
			try {
				int day = Integer.valueOf(value);
				Calendar calendar = new GregorianCalendar(1900,0,-1);
				Date d = calendar.getTime();
				Date dd = DateUtils.addDays(d, day);
				DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
				currentCellValue = formater.format(dd);
			}catch (Exception e) {
				return null;
			}
		}
		return currentCellValue;
	}
	
	/**
	 * 删除7天之前的文件
	 * @param pathurl 文件夹地址
	 */
	public static void deleteFile7(String pathurl) {
		Date date1 = new Date();
        Calendar calendar = Calendar.getInstance(); //得到日历
        calendar.setTime(date1);//把当前时间赋给日历
        calendar.add(Calendar.DAY_OF_MONTH, -7); //设置为7天之前，可根据需求进行修改
        Date date2 = calendar.getTime();//获取7天之前的时间
        //转换成时间戳
        long t1 = date2.getTime();
        try {
            File f = new File(pathurl);
            File[] files = f.listFiles();
            if(files != null) {
            	for (int i = 0; i < files.length; i++) {
            		//获取文件路径
            		String filePath = files[i].getCanonicalPath();
            		//获取文件最后一次被修改的时间
            		Long lastTime = files[i].lastModified();
            		if (lastTime < t1) {
            			//判断文件是否存在和是否是一个标准文件
            			if (files[i].exists() && files[i].isFile()) {
            				File file = new File(filePath);
            				//删除
            				file.delete();
            			}
            		}
            	}
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
	}
	
	/**
	 * 除去筛选列
	 * @param tempTableColumn
	 * @param cloumArray
	 * @param titleArray
	 * @return
	 */
	public static Map<String, String []> getExportCloum(String tempTableColumn, String [] cloumArray, String [] titleArray){
		Map<String, String[]> map = new HashMap<>();
		if(StrUtil.isNotEmpty(tempTableColumn)) {
        	JSONObject json = new JSONObject(tempTableColumn);
        	int len = cloumArray.length;
        	List<String> list1 = new ArrayList<String>();
        	List<String> list2 = new ArrayList<String>();
        	for(int i=0; i<len; i++) {
        		String value = cloumArray[i];
        		if(json.isNull(value) || !json.getBoolean(value)) {
        			list1.add(value);
        			list2.add(titleArray[i]);
        		}
        	}
        	cloumArray = list1.toArray(new String[list1.size()]);
        	titleArray = list2.toArray(new String[list2.size()]);
        }
		map.put("cloumArray", cloumArray);
		map.put("titleArray", titleArray);
		return map;
	}
	
	/**
	 * Excel样式
	 * @param wb
	 * @param mark
	 * @return
	 */
	public static HSSFCellStyle getFont(HSSFWorkbook wb,int mark) {
		HSSFCellStyle styleBg = wb.createCellStyle();
		HSSFFont font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);
        if(mark == 1) {
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            font.setColor(HSSFColor.WHITE.index);
            styleBg.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
            styleBg.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        }
        styleBg.setFont(font);
        styleBg.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个居中格式
        styleBg.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平
        styleBg.setWrapText(true);
       /* styleBg.setBorderLeft((short) 1);// 边框样式
        styleBg.setBorderBottom((short) 1);
        styleBg.setBorderRight((short) 1);
        styleBg.setBorderTop((short) 1);*/
        return styleBg;
	}
	
	
	/**
	 * 删除相关所有文件
	 * @param path
	 * @param filename
	 */
	public static void delAllFile(String path, String filename) {
		File f = new File(path);
		File[] list = f.listFiles();
		if(list != null && list.length > 0) {
			SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
			SimpleDateFormat df2 = new SimpleDateFormat("yyyyMMdd");
			for(File file : list) {
				String name = file.getName();
				if((name.indexOf(".xls") != -1 || name.indexOf(".xlsx") != -1) && name.indexOf(filename.replaceAll(".xls", "").replaceAll(".xlsx", "")) != -1) {
					String s = name.replaceAll(filename, "").replaceAll(".xls", "").replaceAll(".xlsx", ""); 
					try {
						String d1 = df2.format(df.parse(s));
						String d2 = df2.format(new Date());
						//时间是一直大的，如： 20220520 小于20220521
						if(Long.parseLong(d1) < Long.parseLong(d2)) {
							file.delete();
						}
					} catch (ParseException e) {
						e.printStackTrace();
					}
				}
			}
		}
	}
	
	
	
	
	
}
